package com.cloudkinto.service.finance.impl;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.cloudkinto.common.common.DateUtils;
import com.cloudkinto.common.constant.GlobalConstants;
import com.cloudkinto.common.constant.SysConstant;
import com.cloudkinto.common.exception.BizException;
import com.cloudkinto.common.utils.ExcelUtils;
import com.cloudkinto.common.utils.dict.StaticDict;
import com.cloudkinto.dao.*;
import com.cloudkinto.entity.OrderOutputPackageDetailDo;
import com.cloudkinto.entity.OrderTrackBillDo;
import com.cloudkinto.entity.OrderTrackDetailDo;
import com.cloudkinto.extentity.track.*;
import com.cloudkinto.service.finance.OrderTrackBillService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import com.cloudkinto.common.web.PageResult;
import com.cloudkinto.common.web.SingleResult;

import com.cloudkinto.service.finance.OrderTrackDetailService;
import com.cloudkinto.service.finance.vo.exportVo.OrderTrackBillExportVo;
import com.cloudkinto.service.finance.vo.track.*;
import com.cloudkinto.service.storage.StorageService;
import lombok.Data;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.beans.factory.annotation.Autowired;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.util.CollectionUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * <p>
 * service实现类
 * </p>
 *
 * @author hua
 * @since 2023-08-02
 */
@Service
@Transactional(rollbackFor = Exception.class)//事务回滚
public class OrderTrackBillServiceImpl extends ServiceImpl<OrderTrackBillDao, OrderTrackBillDo> implements OrderTrackBillService {
    @Autowired
    private OrderTrackBillDao dao;
    @Autowired
    private OrderTrackDetailDao orderTrackDetailDao;
    @Autowired
    private OrderTrackDetailService orderTrackDetailService;
    @Autowired
    private StorageService storageService;
    @Autowired
    private OrderOutputDao orderOutputDao;
    @Autowired
    private FbaOrderOutputDao fbaOrderOutputDao;
    @Autowired
    private OrderBackDao orderBackDao;
    @Autowired
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;


    @Override
    public SingleResult addInit() {
        return SingleResult.success("");
    }

    @Override
    public Object add(Object req, Long userId) {
        OrderTrackBillDo entityDo = new OrderTrackBillDo();
        BeanUtils.copyProperties(req, entityDo);
        handlerAddBefore(entityDo, userId);
        dao.insert(entityDo);
        return handlerAddAfter(entityDo);
    }

    //新增校验
    private void addCheck(Object req) {

    }

    //做新增之前要执行的业务定制处理
    private void handlerAddBefore(OrderTrackBillDo entityDo, Long userId) {

        entityDo.setDeleteFlag(SysConstant.DELETE_FLAG_FALSE);
    }

    //将新增的do转换成要返回的实体
    private Object handlerAddAfter(OrderTrackBillDo entityDo) {
        Object res = new Object();
        BeanUtils.copyProperties(entityDo, res);
        return res;
    }

    @Override
    public SingleResult updateInit(Long id) {
        return SingleResult.success("");
    }

    @Override
    public Object update(Object req, Long userId) {
        /*OrderTrackBillDo entityDo = dao.selectById(req.getId());
        BeanUtils.copyProperties(req, entityDo);
        handlerUpdateBefore(entityDo, userId);
        dao.updateById(entityDo);
        return handlerUpdateAfter(entityDo);*/
        return null;
    }

    //编辑校验
    private void updateCheck(Object req) {

    }

    //做修改之前要执行的业务定制处理
    private void handlerUpdateBefore(OrderTrackBillDo entityDo, Long userId) {

    }

    //做修改之后要执行的业务定制处理
    private Object handlerUpdateAfter(OrderTrackBillDo entityDo) {
        Object res = new Object();
        BeanUtils.copyProperties(entityDo, res);
        return res;
    }

    @Override
    public int delete(Long id, Long userId) {
        OrderTrackBillDo entityDo = dao.selectById(id);
        handlerDeleteBefore(entityDo, userId);
        return dao.deleteById(id);
    }

    //做删除之前要执行的业务定制处理
    private void handlerDeleteBefore(OrderTrackBillDo entityDo, Long userId) {
    }

    @Override
    public Object detail(Long id) {
        OrderTrackBillDo entityDo = dao.selectById(id);
        entityDo.getTrackAmount();
        Object res = new Object();
        BeanUtils.copyProperties(entityDo, res);
        return handlerDetailAfter(res);
    }

    //查询明细之后要执行的业务定制处理
    private Object handlerDetailAfter(Object res) {
        return res;
    }

    @Override
    public SingleResult pageInit() {
        Map<String, Object> result = new HashMap<>();
        result.put("storageList", storageService.getStorageList());

        List<Map<String, Object>> trackList = new ArrayList<>();
        for (StaticDict.Track_Type value : StaticDict.Track_Type.values()) {
            Map<String, Object> map = new HashMap<>();
            map.put("id", value.getValue());
            map.put("value", value.getText());
            trackList.add(map);
        }
        result.put("trackList", trackList);

        List<Map<String, Object>> matchStatusList = new ArrayList<>();
        for (StaticDict.Track_Match_Status value : StaticDict.Track_Match_Status.values()) {
            Map<String, Object> map = new HashMap<>();
            map.put("id", value.getValue());
            map.put("value", value.getText());
            matchStatusList.add(map);
        }
        result.put("matchStatusList", matchStatusList);
        return SingleResult.success(result);
    }

    @Override
    public List<Object> getList(Map<String, String> map) {
        QueryWrapper<OrderTrackBillDo> wrapper = queryBuild(map);
        List<OrderTrackBillDo> list = this.list(wrapper);
        List<Object> result = handlerListAfter(list);
        return result;
    }

    @Override
    public PageResult getListPage(Map<String, String> map) {
        IPage<OrderTrackPageDto> page = pageInit(map);
        List<OrderTrackPageDto> list = dao.listPage(page, map);
        List<OrderTrackBillRes> resList = new ArrayList<>();
        for (OrderTrackPageDto dto : list) {
            OrderTrackBillRes res = new OrderTrackBillRes();
            BeanUtils.copyProperties(dto, res);
            res.setTrackName(StaticDict.Track_Type.getText(dto.getTrackId()));
            //获取物流 信息
            res.setList(this.getTrackInfo(dto.getId(), dto.getOrderType()));
            resList.add(res);
        }
        PageResult result = new PageResult(resList, (int) page.getCurrent(), (int) page.getSize(), (int) page.getTotal(), (int) page.getPages());
        return result;
    }

    @Override
    public List<OrderTrackNumberDto> getTrackInfo(Long id, String orderType) {
        //再获取 具体快递单号 快递单号 和订单 多对一
        List<OrderTrackNumberDto> trackList = new ArrayList<>();
        if ("output".equals(orderType)) {
            //一件代发 存在 多个快递单号
            trackList = dao.getOutputTrackNumber(id);
        } else if ("fbaOutput".equals(orderType)) {
            //fba转运  原单号只有一个
            trackList = dao.getFbaOutputTrackNumber(id);
        } else if ("back".equals(orderType)) {
            //退货单
            trackList = dao.getBackTrackNumber(id);
        }
        List<Long> ids = trackList.stream().map(OrderTrackNumberDto::getId).filter(Objects::nonNull).collect(Collectors.toList());
        if (ids.size() == 0) {
            return trackList;
        }
        Map<Long, List<TrackDetailDto>> listMap = orderTrackDetailDao.getDetail(ids).stream().collect(Collectors.groupingBy(TrackDetailDto::getTrackBillId));

        //在获取 单号 下的具体包裹 运费 以及运费档
        for (OrderTrackNumberDto trackNumber : trackList) {
            if (trackNumber.getId() == null) {
                continue;
            }
            List<TrackDetailDto> detailDtoList = listMap.get(trackNumber.getId());
            if (detailDtoList != null) {
                trackNumber.setList(detailDtoList);
            }
        }
        return trackList;
    }

    //查询分页列表之后对列表数据的处理
    private List<Object> handlerListAfter(List<OrderTrackBillDo> list) {
        List<Object> result = new ArrayList<>();
        return result;
    }

    private IPage<OrderTrackPageDto> pageInit(Map<String, String> map) {
        IPage<OrderTrackPageDto> page = new Page();
        page.setSize(StringUtils.isNotBlank(map.get("pageSize")) ? Long.parseLong(map.get("pageSize")) : 10);
        page.setCurrent(StringUtils.isNotBlank(map.get("current")) ? Long.parseLong(map.get("current")) : 1);
        return page;
    }

    private QueryWrapper<OrderTrackBillDo> queryBuild(Map<String, String> map) {
        QueryWrapper<OrderTrackBillDo> wrapper = new QueryWrapper<>();
        if (StringUtils.isNotBlank(map.get("keyword"))) {
            wrapper.like("keyword", map.get("keyword"));
        }
        return wrapper;
    }


    @Override
    public void importTrackBill(MultipartFile file, String year, Long userId) throws IOException {
        if (StringUtils.isBlank(year)) {
            throw new BizException(SysConstant.No_Data);
        }
        if (file == null) {
            throw new BizException(SysConstant.Pls_Choose_File);
        }
        if (StringUtils.isBlank(file.getName())) {
            throw new BizException(SysConstant.File_Name_Null);
        }
        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
        if (!GlobalConstants.IMG_TYPE_XLS.equals(suffix.toUpperCase()) &&
                !GlobalConstants.IMG_TYPE_XLSX.equals(suffix.toUpperCase()) && !GlobalConstants.IMG_TYPE_CSV.equals(suffix.toUpperCase())) {
            throw new BizException(SysConstant.File_Form_Incorrect);
        }

        List<OrderTrackBillVo> billList = new ArrayList<>();
        Date beginDate = null;
        Date endDate = null;

        if (GlobalConstants.IMG_TYPE_CSV.equals(suffix.toUpperCase()) || "PDF".equals(suffix.toUpperCase())) {
//                exportVoList = CsvImportUtil.importCsv(file.getInputStream(), OrderIncomeImportVo.class, new CsvImportParams());
            InputStream inputStream = file.getInputStream();
//            byte[] bytes = null;
//            ByteArrayOutputStream swapStream = new ByteArrayOutputStream();
//            byte[] buff = new byte[100];
//            int rc = 0;
//            while ((rc = inputStream.read(buff, 0, 100)) > 0) {
//                swapStream.write(buff, 0, rc);
//            }
//            bytes = swapStream.toByteArray();
//            String data = new String(bytes, "Shift_JIS");
//            List<String> dateStringList = new ArrayList<>(Arrays.asList(data.split("\n")));

            StringBuilder buffer1 = new StringBuilder();
            InputStreamReader is = new InputStreamReader(inputStream, "Shift_JIS");
            char[] chars = new char[100];
            int index = 0;
            while ((index = is.read(chars, 0, 100)) > 0) {
                buffer1.append(chars, 0, index);
            }
            List<String> dateStringList = new ArrayList<>(Arrays.asList(buffer1.toString().split("\n")));

            String firstColumn = "";
            String second = "";

            for (int i = 0; i < dateStringList.size(); i++) {
                //双引号内的逗号不分割 双引号外的逗号进行分割
                String dataString = dateStringList.get(i);
                StringBuilder buffer = new StringBuilder(dataString);
                for (int j = 0; j < dataString.toCharArray().length; j++) {
                    if (j == 0 || j == dataString.toCharArray().length - 1) {
                        continue;
                    }
                    if ("\"".equals(String.valueOf(dataString.toCharArray()[j]))) {
                        if (!",".equals(String.valueOf(dataString.toCharArray()[j + 1])) || !"\"".equals(String.valueOf(dataString.toCharArray()[j + 2]))) {
                            buffer.replace(j, j + 1, "");
                        } else {
                            j += 2;
                        }
                    }
                }
                dataString = buffer.toString();
                String[] strArr = dataString.split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)", -1);
                for (int z = 0; z < strArr.length; z++) {
                    String str = strArr[z];
                    if (str != null && str.length() >= 2) {
                        if (str.indexOf("\"") == 0) {
                            str = str.substring(1, str.length());   //去掉第一个 "
                        }
                        if (str.lastIndexOf("\"") == (str.length() - 1)) {
                            str = str.substring(0, str.length() - 1);  //去掉最后一个 "
                        }
                        str = str.replaceAll("\"\"", "\"");//把两个双引号换成一个双引号
                        strArr[z] = str;
                    }
                }
                if (i == 0) {
                    firstColumn = strArr[0];
                    second = strArr[1];
                    continue;
                }
                OrderTrackBillVo billVo = new OrderTrackBillVo();
                if ("店所コード".equals(firstColumn)) {//黑猫
//                    billVo.setTrackType("Yamato");
                    billVo.setTrackId(StaticDict.Track_Type.YamatoExpress.getValue());
                    billVo.setTrackNumber(strArr[4].replaceAll("-", "").trim());
                    billVo.setDate(DateUtils.stringFormatToDate(year + "/" + strArr[3], "yyyy/MM/dd"));
                    billVo.setDateStr(DateUtils.dateFormatToString(billVo.getDate(), "yyyyMM"));
                    if (StringUtils.isBlank(billVo.getTrackNumber()) || StringUtils.isBlank(billVo.getDateStr())) {
                        continue;
                    }

                    billVo.setCount(Integer.parseInt(strArr[8]));
                    billVo.setTrackAmount(new BigDecimal(strArr[11]));//12
                    billVo.setOtherAmount4(new BigDecimal(strArr[12]));//13
                    billVo.setOtherAmount2(new BigDecimal(strArr[13]));//14
                    billVo.setTaxFee(new BigDecimal(strArr[15]));//15

                    TrackDetailVo detailVo = new TrackDetailVo();
                    detailVo.setCount(billVo.getCount());
                    detailVo.setSize(strArr[7]);
                    detailVo.setSizeUnit("サイズ");
                    detailVo.setAmount(billVo.getTrackAmount());

                    List<TrackDetailVo> list = new ArrayList<>();
                    list.add(detailVo);
                    billVo.setList(list);

                } else if ("請求書番号".equals(firstColumn) && "請求書締日".equals(second)) {
                    //佐川总表
                    billVo.setTrackId(StaticDict.Track_Type.SagawaExpress.getValue());
                    String trackNumber = strArr[2].trim();
                    while ((trackNumber.charAt(0) + "").equals("0")) {
                        trackNumber = trackNumber.substring(1);
                    }
                    billVo.setTrackNumber(trackNumber);
                    billVo.setDate(DateUtils.stringFormatToDate(strArr[8], "yyyyMMdd"));
                    billVo.setDateStr(DateUtils.dateFormatToString(billVo.getDate(), "yyyyMM"));
                    if (StringUtils.isBlank(billVo.getTrackNumber()) || StringUtils.isBlank(billVo.getDateStr())) {
                        continue;
                    }

                    billVo.setCount(Integer.parseInt(strArr[13]));
                    billVo.setTrackAmount(new BigDecimal(strArr[58]));
                    //消费税  直接抹去小数点
                    billVo.setTaxFee(billVo.getTrackAmount().multiply(new BigDecimal("0.1")).setScale(0, BigDecimal.ROUND_DOWN));
                    billVo.setOtherAmount4(new BigDecimal(strArr[62]));
                    billVo.setOtherAmount2(new BigDecimal(strArr[60]));
                    billVo.setOtherAmount1(new BigDecimal(strArr[59]));
                    billVo.setOtherAmount3(new BigDecimal(strArr[61]));
                    billVo.setOtherAmount5(new BigDecimal(strArr[63]));

                    //包裹 佐川包裹 可能多个，最多8个
                    List<TrackDetailVo> list = new ArrayList<>();
                    //包裹一
                    this.getSagawaPackageCsv(17, strArr, list);
                    this.getSagawaPackageCsv(22, strArr, list);
                    this.getSagawaPackageCsv(27, strArr, list);
                    this.getSagawaPackageCsv(32, strArr, list);
                    this.getSagawaPackageCsv(37, strArr, list);
                    this.getSagawaPackageCsv(42, strArr, list);
                    this.getSagawaPackageCsv(47, strArr, list);
                    this.getSagawaPackageCsv(52, strArr, list);
                    billVo.setList(list);
                }
                else if ("請求書番号".equals(firstColumn) && "お客様コード".equals(second)) {
                    //佐川基础表
                    billVo.setTrackId(StaticDict.Track_Type.SagawaExpress.getValue());
                    String trackNumber = strArr[9].trim();
                    while ((trackNumber.charAt(0) + "").equals("0")) {
                        trackNumber = trackNumber.substring(1);
                    }
                    billVo.setTrackNumber(trackNumber);
                    billVo.setDate(DateUtils.stringFormatToDate(year+strArr[8].trim(), "yyyyMMdd"));
                    billVo.setDateStr(DateUtils.dateFormatToString(billVo.getDate(), "yyyyMM"));
                    if (StringUtils.isBlank(billVo.getTrackNumber()) || StringUtils.isBlank(billVo.getDateStr())) {
                        continue;
                    }

                    billVo.setCount(Integer.parseInt(strArr[14]));
                    billVo.setTrackAmount(new BigDecimal(strArr[16]));
                    //消费税  直接抹去小数点
                    billVo.setTaxFee(billVo.getTrackAmount().multiply(new BigDecimal("0.1")).setScale(0, BigDecimal.ROUND_DOWN));
                    billVo.setOtherAmount2(new BigDecimal(strArr[18]));
                    billVo.setOtherAmount1(new BigDecimal(strArr[17]));
                    billVo.setOtherAmount3(new BigDecimal(strArr[19]));

                    //包裹 佐川总表 按一个算
                    TrackDetailVo detailVo = new TrackDetailVo();
                    detailVo.setCount(billVo.getCount());
//                    detailVo.setSize(strArr[7]);
//                    detailVo.setSizeUnit("サイズ");
                    detailVo.setAmount(billVo.getTrackAmount());

                    List<TrackDetailVo> list = new ArrayList<>();
                    list.add(detailVo);
                    billVo.setList(list);

                }
                else {
                    throw new BizException(SysConstant.File_Form_Incorrect);
                }
                if (beginDate == null || billVo.getDate().before(beginDate)) {
                    beginDate = billVo.getDate();
                }

                if (endDate == null || billVo.getDate().after(endDate)) {
                    endDate = billVo.getDate();
                }
                billList.add(billVo);
            }


        } else {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
            XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);

            XSSFRow headerRow = sheetAt.getRow(0); //表头行
            //判断 是黑猫 还是佐川文件
            int lastRowNum = sheetAt.getLastRowNum();
            if (headerRow.getCell(0) != null && "店所コード".equals(headerRow.getCell(0).getStringCellValue())) {
                //黑猫
                for (int i = 1; i <= lastRowNum; i++) {
                    XSSFRow row = sheetAt.getRow(i);
                    OrderTrackBillVo billVo = new OrderTrackBillVo();
//                    billVo.setTrackType("Yamato");
                    billVo.setTrackId(StaticDict.Track_Type.YamatoExpress.getValue());
                    //快递单号 第五列
                    if (row.getCell(4) != null) {
                        row.getCell(4).setCellType(CellType.STRING);
                        String trackNumber = row.getCell(4).getStringCellValue().replaceAll("-", "").trim();
                        billVo.setTrackNumber(trackNumber);
                    }
                    //日期 第四列
                    if (row.getCell(3) != null) {
                        row.getCell(3).setCellType(CellType.STRING);
                        String date = row.getCell(3).getStringCellValue();
                        billVo.setDate(DateUtils.stringFormatToDate(year + "/" + date, "yyyy/MM/dd"));
                        billVo.setDateStr(DateUtils.dateFormatToString(billVo.getDate(), "yyyyMM"));
                    }
                    if (StringUtils.isBlank(billVo.getTrackNumber()) || StringUtils.isBlank(billVo.getDateStr())) {
                        continue;
                    }
                    //个数 第九列
                    if (row.getCell(8) != null) {
                        row.getCell(8).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(8).getStringCellValue())) {
                            billVo.setCount(Integer.parseInt(row.getCell(8).getStringCellValue()));
                        }
                    }
                    //运费 第十三列
                    if (row.getCell(12) != null) {
                        row.getCell(12).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(12).getStringCellValue())) {
                            billVo.setTrackAmount(new BigDecimal(row.getCell(12).getStringCellValue()));
                        }
                    }
                    //立体金 十四列
                    if (row.getCell(13) != null) {
                        row.getCell(13).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(13).getStringCellValue())) {
                            billVo.setOtherAmount4(new BigDecimal(row.getCell(13).getStringCellValue()));
                        }
                    }
                    //保険料  十五列
                    if (row.getCell(14) != null) {
                        row.getCell(14).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(14).getStringCellValue())) {
                            billVo.setOtherAmount2(new BigDecimal(row.getCell(14).getStringCellValue()));
                        }
                    }
                    //消費税  十六列
                    if (row.getCell(15) != null) {
                        row.getCell(15).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(15).getStringCellValue())) {
                            billVo.setTaxFee(new BigDecimal(row.getCell(15).getStringCellValue()));
                        }
                    }

                    //包裹 黑猫包裹默认就一个
                    TrackDetailVo detailVo = new TrackDetailVo();
                    //包裹中的件数
                    detailVo.setCount(billVo.getCount());
                    //尺寸 第八列
                    if (row.getCell(7) != null) {
                        row.getCell(7).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(7).getStringCellValue())) {
                            detailVo.setSize(row.getCell(7).getStringCellValue());
                            detailVo.setSizeUnit("サイズ");
                        }
                    }
                    //包裹运费
                    detailVo.setAmount(billVo.getTrackAmount());

                    List<TrackDetailVo> list = new ArrayList<>();
                    list.add(detailVo);
                    billVo.setList(list);
                    billList.add(billVo);

                    if (beginDate == null || billVo.getDate().before(beginDate)) {
                        beginDate = billVo.getDate();
                    }

                    if (endDate == null || billVo.getDate().after(endDate)) {
                        endDate = billVo.getDate();
                    }
                    billList.add(billVo);
                }


            } else if (headerRow.getCell(0) != null && "請求書番号".equals(headerRow.getCell(0).getStringCellValue())) {
                //佐川
                for (int i = 1; i <= lastRowNum; i++) {
                    XSSFRow row = sheetAt.getRow(i);
                    OrderTrackBillVo billVo = new OrderTrackBillVo();
//                    billVo.setTrackType("Sagawa");
                    billVo.setTrackId(StaticDict.Track_Type.SagawaExpress.getValue());
                    //快递单号 第三列
                    if (row.getCell(2) != null) {
                        String trackNumber = row.getCell(2).getStringCellValue().trim();
                        billVo.setTrackNumber(trackNumber);
                    }
                    //日期 第二列 20230430 第九列 出荷日
                    if (row.getCell(8) != null) {
                        String date = row.getCell(8).getStringCellValue();
                        billVo.setDate(DateUtils.stringFormatToDate(date, "yyyyMMdd"));
                        billVo.setDateStr(DateUtils.dateFormatToString(billVo.getDate(), "yyyyMM"));
                    }
                    if (StringUtils.isBlank(billVo.getTrackNumber()) || StringUtils.isBlank(billVo.getDateStr())) {
                        continue;
                    }
                    //総個数 第十四列
                    if (row.getCell(13) != null) {
                        row.getCell(13).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(13).getStringCellValue())) {
                            billVo.setCount(Integer.parseInt(row.getCell(13).getStringCellValue()));
                        }
                    }
                    //运费 第五十九列
                    if (row.getCell(58) != null) {
                        row.getCell(58).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(58).getStringCellValue())) {
                            billVo.setTrackAmount(new BigDecimal(row.getCell(58).getStringCellValue()));
                            //消费税  直接抹去小数点
                            billVo.setTaxFee(billVo.getTrackAmount().multiply(new BigDecimal("0.1")).setScale(0, BigDecimal.ROUND_DOWN));
                        }
                    }
                    //立体金 六十三列
                    if (row.getCell(62) != null) {
                        row.getCell(62).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(62).getStringCellValue())) {
                            billVo.setOtherAmount4(new BigDecimal(row.getCell(62).getStringCellValue()));
                        }
                    }
                    //保険料  六十一列
                    if (row.getCell(60) != null) {
                        row.getCell(60).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(60).getStringCellValue())) {
                            billVo.setOtherAmount2(new BigDecimal(row.getCell(60).getStringCellValue()));
                        }
                    }
                    //代引手数料 六十列
                    if (row.getCell(59) != null) {
                        row.getCell(59).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(59).getStringCellValue())) {
                            billVo.setOtherAmount1(new BigDecimal(row.getCell(59).getStringCellValue()));
                        }
                    }
                    //中継料 六十二列
                    if (row.getCell(61) != null) {
                        row.getCell(61).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(61).getStringCellValue())) {
                            billVo.setOtherAmount3(new BigDecimal(row.getCell(61).getStringCellValue()));
                        }
                    }
                    //合算諸料金金額 六十四列
                    if (row.getCell(63) != null) {
                        row.getCell(63).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(63).getStringCellValue())) {
                            billVo.setOtherAmount5(new BigDecimal(row.getCell(63).getStringCellValue()));
                        }
                    }

                    //包裹 佐川包裹 可能多个，最多8个
                    List<TrackDetailVo> list = new ArrayList<>();
                    //包裹一
                    this.getSagawaPackageXls(17, row, list);
                    this.getSagawaPackageXls(22, row, list);
                    this.getSagawaPackageXls(27, row, list);
                    this.getSagawaPackageXls(32, row, list);
                    this.getSagawaPackageXls(37, row, list);
                    this.getSagawaPackageXls(42, row, list);
                    this.getSagawaPackageXls(47, row, list);
                    this.getSagawaPackageXls(52, row, list);
                    billVo.setList(list);
                    billList.add(billVo);

                    if (beginDate == null || billVo.getDate().before(beginDate)) {
                        beginDate = billVo.getDate();
                    }

                    if (endDate == null || billVo.getDate().after(endDate)) {
                        endDate = billVo.getDate();
                    }
                    billList.add(billVo);
                }
            }
            //発行日 2023/07/31
            //請求No.074520230731
            else if (headerRow.getCell(17) != null && StringUtils.isNotBlank(headerRow.getCell(17).getStringCellValue())
                    && headerRow.getCell(17).getStringCellValue().startsWith("発行日") && headerRow.getCell(17).getStringCellValue().contains("請求No.")){
                //西农
                for (int i = 1; i <= lastRowNum; i++) {
                    XSSFRow row = sheetAt.getRow(i);
                    if (row.getCell(0) == null) {
                        continue;
                    }
                    row.getCell(0).setCellType(CellType.STRING);
                    String date = row.getCell(0).getStringCellValue();
                    if (StringUtils.isBlank(date)) {
                        continue;
                    }
                    if (!date.matches("[0-9]{4}[/][0-9]{2}[/][0-9]{2}")){
                        continue;
                    }
                    OrderTrackBillVo billVo = new OrderTrackBillVo();
                    billVo.setTrackId(StaticDict.Track_Type.XinongExpress.getValue());
                    //日期
                    billVo.setDate(DateUtils.stringFormatToDate(date, "yyyy/MM/dd"));
                    billVo.setDateStr(DateUtils.dateFormatToString(billVo.getDate(), "yyyyMM"));
                    //快递单号
                    if (row.getCell(9) != null) {
                        row.getCell(9).setCellType(CellType.STRING);
                        String trackNumber = row.getCell(9).getStringCellValue().replaceAll("-", "").trim();
                        billVo.setTrackNumber(trackNumber);
                    }
                    if (StringUtils.isBlank(billVo.getTrackNumber()) || StringUtils.isBlank(billVo.getDateStr())) {
                        continue;
                    }

                    //数量
                    if (row.getCell(5) != null) {
                        row.getCell(5).setCellType(CellType.STRING);
                        String count = row.getCell(5).getStringCellValue();
                        if (StringUtils.isNotBlank(count)) {
                            billVo.setCount(Integer.parseInt(count));
                        }
                    }

                    //摘要
                    if (row.getCell(11) != null) {
                        row.getCell(11).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(11).getStringCellValue())) {
                            billVo.setSummary(row.getCell(11).getStringCellValue());
                        }
                    }
                    //保険料
                    if (row.getCell(14) != null) {
                        row.getCell(14).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(14).getStringCellValue())) {
                            billVo.setOtherAmount2(new BigDecimal(row.getCell(14).getStringCellValue()));
                        }
                    }
                    //有料道路料	即中継料
                    if (row.getCell(15) != null) {
                        row.getCell(15).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(15).getStringCellValue())) {
                            billVo.setOtherAmount3(new BigDecimal(row.getCell(15).getStringCellValue()));
                        }
                    }
                    //运费
                    if (row.getCell(18) != null) {
                        row.getCell(18).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(18).getStringCellValue())) {
                            billVo.setTrackAmount(new BigDecimal(row.getCell(18).getStringCellValue()));
                        }
                    }
                    //消费税
                    if (row.getCell(20) != null) {
                        row.getCell(20).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(20).getStringCellValue())) {
                            billVo.setTaxFee(new BigDecimal(row.getCell(20).getStringCellValue()));
                        }
                    }

                    //包裹 西农默认就一个
                    TrackDetailVo detailVo = new TrackDetailVo();
                    //包裹中的件数
                    detailVo.setCount(billVo.getCount());
                    //重量
                    if (row.getCell(6) != null) {
                        row.getCell(6).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(6).getStringCellValue())) {
                            detailVo.setSize(row.getCell(6).getStringCellValue());
                        }
                    }
                    //单位
                    if (row.getCell(7) != null) {
                        row.getCell(7).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(7).getStringCellValue())) {
                            detailVo.setSizeUnit(row.getCell(7).getStringCellValue());
                        }
                    }
                    //包裹运费
                    detailVo.setAmount(billVo.getTrackAmount());

                    List<TrackDetailVo> list = new ArrayList<>();
                    list.add(detailVo);
                    billVo.setList(list);
                    billList.add(billVo);

                    if (beginDate == null || billVo.getDate().before(beginDate)) {
                        beginDate = billVo.getDate();
                    }

                    if (endDate == null || billVo.getDate().after(endDate)) {
                        endDate = billVo.getDate();
                    }
                }
            }
            else if (headerRow.getCell(0) != null && "寄託者コード".equals(headerRow.getCell(0).getStringCellValue())
            && headerRow.getCell(1) != null && "伝票日付".equals(headerRow.getCell(1).getStringCellValue())) {
                //佐川SKL 其中 快递单号可能为空 4T车的费用
                for (int i = 1; i <= lastRowNum; i++) {
                    XSSFRow row = sheetAt.getRow(i);
                    if (row.getCell(1) == null) {
                        continue;
                    }
                    row.getCell(1).setCellType(CellType.STRING);
                    String date = row.getCell(1).getStringCellValue();
                    if (StringUtils.isBlank(date)) {
                        continue;
                    }
                    if (!date.matches("[0-9]{8}")) {
                        continue;
                    }
                    OrderTrackBillVo billVo = new OrderTrackBillVo();
                    billVo.setTrackId(StaticDict.Track_Type.SagawaExpress.getValue());
                    //日期
                    billVo.setDate(DateUtils.stringFormatToDate(date, "yyyyMMdd"));
                    billVo.setDateStr(DateUtils.dateFormatToString(billVo.getDate(), "yyyyMM"));
                    //快递单号
                    if (row.getCell(8) != null) {
                        row.getCell(8).setCellType(CellType.STRING);
                        String trackNumber = row.getCell(8).getStringCellValue().replaceAll("-", "").trim();
                        billVo.setTrackNumber(trackNumber);
                    }
                    else {
                        billVo.setTrackNumber("");
                    }
                    //数量
                    if (row.getCell(6) != null) {
                        row.getCell(6).setCellType(CellType.STRING);
                        String count = row.getCell(6).getStringCellValue();
                        if (StringUtils.isNotBlank(count)) {
                            billVo.setCount(Integer.parseInt(count));
                        }
                    }

                    //摘要
                    if (row.getCell(11) != null) {
                        row.getCell(11).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(11).getStringCellValue())) {
                            billVo.setSummary(row.getCell(11).getStringCellValue());
                        }
                    }

                    //运费
                    if (row.getCell(9) != null) {
                        row.getCell(9).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(9).getStringCellValue())) {
                            billVo.setTrackAmount(new BigDecimal(row.getCell(9).getStringCellValue()));
                            //消费税  直接抹去小数点
                            billVo.setTaxFee(billVo.getTrackAmount().multiply(new BigDecimal("0.1")).setScale(0, BigDecimal.ROUND_DOWN));
                        }
                    }

                    //包裹 SKL佐川 默认就一个
                    TrackDetailVo detailVo = new TrackDetailVo();
                    //包裹中的件数
                    detailVo.setCount(billVo.getCount());
                    //重量
                    if (row.getCell(10) != null) {
                        row.getCell(10).setCellType(CellType.STRING);
                        if (StringUtils.isNotBlank(row.getCell(10).getStringCellValue())) {
                            detailVo.setSize(row.getCell(10).getStringCellValue());
                        }
                    }

                    //包裹运费
                    detailVo.setAmount(billVo.getTrackAmount());

                    List<TrackDetailVo> list = new ArrayList<>();
                    list.add(detailVo);
                    billVo.setList(list);
                    billList.add(billVo);

                    if (beginDate == null || billVo.getDate().before(beginDate)) {
                        beginDate = billVo.getDate();
                    }

                    if (endDate == null || billVo.getDate().after(endDate)) {
                        endDate = billVo.getDate();
                    }

                }

            }
            else {
                throw new BizException(SysConstant.File_Form_Incorrect);
            }

        }

        if (beginDate == null || endDate == null) {
            return;
        }
        List<MatchTrackDto> orderList = dao.getMatchTrackList(DateUtils.dateFormatToString(beginDate), DateUtils.dateFormatToString(endDate));
        Map<String, List<MatchTrackDto>> listMap = orderList.stream().collect(Collectors.groupingBy(MatchTrackDto::getTrackNumber));
//        List<OrderTrackBillDo> etList = new ArrayList<>();
        //往数据库 插入数据
        for (OrderTrackBillVo billVo : billList) {
            OrderTrackBillDo et;
            if (StringUtils.isNotBlank(billVo.getTrackNumber())) {
                et = dao.selectOne(new LambdaQueryWrapper<OrderTrackBillDo>()
                        .eq(OrderTrackBillDo::getTrackNumber, billVo.getTrackNumber()).eq(OrderTrackBillDo::getDateStr, billVo.getDateStr()).last(" limit 1"));
            }
            else {
                et = dao.selectOne(new LambdaQueryWrapper<OrderTrackBillDo>()
                        .eq(OrderTrackBillDo::getTrackNumber, "")
                        .eq(OrderTrackBillDo::getDate, DateUtils.dateFormatToString(billVo.getDate())).eq(OrderTrackBillDo::getCount, billVo.getCount())
                        .eq(OrderTrackBillDo::getTrackAmount, billVo.getTrackAmount()).last(" limit 1"));
            }
                //快递单号 是空 的情况。SKL佐川 4T车
            if (et != null) {
                continue;
            }
            et = new OrderTrackBillDo();
            BeanUtils.copyProperties(billVo, et);
            et.setImportBy(userId);
            et.setImportTime(new Date());
            dao.insert(et);

            //再插入包裹
            for (TrackDetailVo detailVo : billVo.getList()) {
                OrderTrackDetailDo detailDo = new OrderTrackDetailDo();
                BeanUtils.copyProperties(detailVo, detailDo);
                detailDo.setTrackBillId(et.getId());
                orderTrackDetailDao.insert(detailDo);
            }

            if (StringUtils.isBlank(et.getTrackNumber())) {
                continue;
            }

            //去匹配 一件代发 fba转运 退货单单号  日前前后十五天
//                MatchTrackDto result = dao.matchTrack(et.getTrackNumber(), DateUtils.dateFormatToString(et.getDate()));
            MatchTrackDto result = listMap.get(et.getTrackNumber()) != null ? listMap.get(et.getTrackNumber()).get(0) : null;
            if (result != null) {
                et.setOrderId(result.getId());
                et.setOrderType(result.getOrderType());
                et.setMatchBy(userId);
                et.setMatchTime(new Date());
                et.setMatchMode("auto");
                dao.updateById(et);
            }
            if (result != null) {
                if ("output".equals(result.getOrderType())) {
                    //获取 订单下的所有 快递单号，并计算多少个原单号 没有匹配。 大于0则部分匹配
                    long count = dao.getOutputTrackNumber(result.getId()).stream().filter(i -> i.getONumber() != null && i.getId() == null).count();
                    orderOutputDao.updateTrackMatch(result.getId(), count == 0 ? 1 : 2);
                } else if ("fbaOutput".equals(result.getOrderType())) {
//                    count = dao.getFbaOutputTrackNumber(result.getId()).stream().filter(i -> i.getONumber() != null && i.getId() == null).count();
                    fbaOrderOutputDao.updateTrackMatch(result.getId(), 1);
                } else if ("back".equals(result.getOrderType())) {
//                    count = dao.getBackTrackNumber(result.getId()).stream().filter(i -> i.getONumber() != null && i.getId() == null).count();
                    orderBackDao.updateTrackMatch(result.getId(), 1);
                }
            }

        }

    }

    public void getSagawaPackageCsv(int index, String[] strArr, List<TrackDetailVo> list) {
        TrackDetailVo detailVo = new TrackDetailVo();
        //包裹中的件数
        detailVo.setCount(Integer.parseInt(strArr[index + 3]));
        if (detailVo.getCount() == 0) {
            return;
        }

        //尺寸
        String size = strArr[index].trim();
        while ((size.charAt(0) + "").equals("0")) {
            size = size.substring(1);
        }
        detailVo.setSize(size);

        //尺寸单位
        detailVo.setSizeUnit(strArr[index + 2]);


        //包裹运费
        detailVo.setAmount(new BigDecimal(strArr[index + 4]));

        list.add(detailVo);
    }


    public void getSagawaPackageXls(int index, XSSFRow row, List<TrackDetailVo> list) {
        TrackDetailVo detailVo = new TrackDetailVo();
        //尺寸 第八列
        if (row.getCell(index) != null) {
            row.getCell(index).setCellType(CellType.STRING);
            String size = row.getCell(index).getStringCellValue();
            if (StringUtils.isNotBlank(size)) {
                detailVo.setSize(size);
            }
        }
        //尺寸单位
        if (row.getCell(index + 2) != null) {
            String sizeUnit = row.getCell(index).getStringCellValue();
            detailVo.setSizeUnit(sizeUnit);
        }
        //包裹中的件数
        if (row.getCell(index + 3) != null) {
            row.getCell(index).setCellType(CellType.STRING);
            String count = row.getCell(index).getStringCellValue();
            if (StringUtils.isNotBlank(count)) {
                detailVo.setCount(Integer.parseInt(count));
            }
        }
        //包裹运费
        if (row.getCell(index + 4) != null) {
            row.getCell(index).setCellType(CellType.STRING);
            String amount = row.getCell(index).getStringCellValue();
            if (StringUtils.isNotBlank(amount)) {
                detailVo.setAmount(new BigDecimal(amount));
            }
        }
        if (detailVo.getCount() == null) {
            return;
        }

        list.add(detailVo);
    }

    /**
     * 修改快递公司
     */
    @Override
    public void updateTrackCompany(UpdateTrackCompanyReq req) {
        LambdaUpdateWrapper<OrderTrackBillDo> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(OrderTrackBillDo::getOrderId, req.getId()).eq(OrderTrackBillDo::getOrderType, req.getOrderType());
        updateWrapper.set(OrderTrackBillDo::getTrackId, req.getTrackId());
        this.update(updateWrapper);
    }

    /**
     * 获取未匹配列表
     */
    @Override
    public PageResult getUnMatchList(Map<String, String> map) {
        IPage<OrderTrackUnMatchDto> page = pageInitUn(map);
        List<OrderTrackUnMatchDto> list = dao.getUnMatchList(page, map);
        PageResult result = new PageResult(list, (int) page.getCurrent(), (int) page.getSize(), (int) page.getTotal(), (int) page.getPages());

        return result;
    }

    private IPage<OrderTrackUnMatchDto> pageInitUn(Map<String, String> map) {
        IPage<OrderTrackUnMatchDto> page = new Page();
        page.setSize(StringUtils.isNotBlank(map.get("pageSize")) ? Long.parseLong(map.get("pageSize")) : 10);
        page.setCurrent(StringUtils.isNotBlank(map.get("current")) ? Long.parseLong(map.get("current")) : 1);
        return page;
    }


    /**
     * 匹配账单
     */
    @Override
    public void matchTrackBill(MatchTrackBillReq req, Long userId) {
        if (req.getBillIds() == null || req.getBillIds().size() == 0) {
            throw new BizException(SysConstant.Pls_Choose_Data);
        }
        List<OrderTrackBillDo> list = dao.selectBatchIds(req.getBillIds());
        for (OrderTrackBillDo et : list) {
            if (et.getOrderId() != null) {
                continue;
            }
            et.setMatchBy(userId);
            et.setMatchMode("manual");
            et.setMatchTime(new Date());
            et.setOrderId(req.getId());
            et.setOrderType(req.getOrderType());
            dao.updateById(et);
        }
    }


    @Override
    public void updateAmount(UpdateAmountReq req, Long userId) {
        OrderTrackBillDo et = dao.selectById(req.getId());
        if (et == null) {
            throw new BizException(SysConstant.No_Data);
        }
        et.setTrackAmount(req.getTrackAmount());
        et.setTaxFee(req.getTaxFee());
        et.setOtherAmount1(req.getOtherAmount1());
        et.setOtherAmount2(req.getOtherAmount2());
        et.setOtherAmount3(req.getOtherAmount3());
        et.setOtherAmount4(req.getOtherAmount4());
        et.setOtherAmount5(req.getOtherAmount5());
        et.setUpdateBy(userId);
        et.setUpdateTime(new Date());
        dao.updateById(et);

        //更新子表
        for (TrackDetailDto child : req.getList()) {
            LambdaUpdateWrapper<OrderTrackDetailDo> up = new LambdaUpdateWrapper<>();
            up.eq(OrderTrackDetailDo::getId, child.getId());
            up.eq(OrderTrackDetailDo::getTrackBillId, child.getTrackBillId());
            up.set(OrderTrackDetailDo::getAmount, child.getAmount());
            up.set(OrderTrackDetailDo::getUpdateBy, userId);
            up.set(OrderTrackDetailDo::getUpdateTime, new Date());
            orderTrackDetailService.update(up);
        }

    }

    @Override
    public void export(Map<String, String> map, HttpServletResponse response) throws IOException {
        List<OrderTrackPageDto> list = dao.listPage(map);

        List<OrderTrackBillExportVo> exportVoList = new ArrayList<>();
        for (OrderTrackPageDto dto : list) {
            OrderTrackBillExportVo exportVo = new OrderTrackBillExportVo();
            BeanUtils.copyProperties(dto, exportVo);
            exportVo.setTrackName(StaticDict.Track_Type.getText(dto.getTrackId()));
            if (!"-".equals(dto.getProfit()) && !"".equals(dto.getProfit())) {
                exportVo.setProfit(dto.getProfit() + "%");
            }
            exportVoList.add(exportVo);
        }
        ExcelUtils.exportExcel(exportVoList, OrderTrackBillExportVo.class, "账单核对.xlsx", new ExportParams(), response);
    }


    @Override
    public void markMatched(Long id, String orderType) {
        if ("output".equals(orderType)) {
            orderOutputDao.updateTrackMatch(id, 1);
        } else if ("fbaOutput".equals(orderType)) {
            fbaOrderOutputDao.updateTrackMatch(id, 1);
        } else if ("back".equals(orderType)) {
            orderBackDao.updateTrackMatch(id, 1);
        }
    }

}
